﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.IM;
using yunbo.Model.SM;

namespace yunbo.Repositorys.IMRepositorys
{
    public class WMS_CKPDRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        public int PostWMS_CKPD(TJWMS_CKPD wMS_CKPD)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[wms_ckpd]([ckpd_code],[ckpd_type],[ckpd_ck],[ckpd_jhpdsj],[ckpd_bz],[ckpd_state],[create_by],[create_time],[ckpd_ykzsl])\r\n" +
                    $"values('{wMS_CKPD.ckpd_code}','{wMS_CKPD.ckpd_type}','{wMS_CKPD.ckpd_ck}','{wMS_CKPD.ckpd_jhpdsj}','{wMS_CKPD.ckpd_bz}',1,{wMS_CKPD.create_by},'{DateTime.Now}',0)";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public FYCXWMS_CKPD GetCXWMS_CKPD(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select a.ckpd_ykzsl,a.ckpd_ck,a.ckpd_by,a.ckpd_time,a.ckpd_ykzsl,a.ckpd_id,a.ckpd_code,a.ckpd_type,b.ck_name,a.ckpd_jhpdsj,a.ckpd_bz,a.create_by,c.user_uname,a.create_time from wms_ckpd a\r\n" +
                    $"inner join [dbo].[wbd_ck] b on a.ckpd_ck=b.ck_code\r\n" +
                    $"inner join [dbo].[user] c on a.create_by=c.user_id\r\n" +
                    $"ORDER BY  a.ckpd_id \r\n" +
                    $"OFFSET ({PageNumber}-1)*{PageSize} ROWS\r\n" +
                    $"FETCH NEXT {PageSize} ROWS ONLY;"; 
                List<CXWMS_CKPD> cXWMS_CKPDs = connection.Query<CXWMS_CKPD>(sql).ToList();
                string sql1 = $"select COUNT(*) from wms_ckpd a\r\ninner join [dbo].[wbd_ck] b on a.ckpd_ck=b.ck_code\r\ninner join [dbo].[user] c on a.create_by=c.user_id";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYCXWMS_CKPD fYCXWMS = new FYCXWMS_CKPD()
                {
                    cXWMS_CKPDs = cXWMS_CKPDs,
                    zon = zon,
                };
                return fYCXWMS;
            }
        }
        public int UpdateWMS_CKPD(XGWMS_CKPD wMS_CKPD)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_ckpd] set [ckpd_type]='{wMS_CKPD.ckpd_type}',[ckpd_jhpdsj]='{wMS_CKPD.ckpd_jhpdsj}',[ckpd_bz]='{wMS_CKPD.ckpd_bz}',[update_by]={wMS_CKPD.update_by},[update_time]='{DateTime.Now}' where [ckpd_code]='{wMS_CKPD.ckpd_code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int UpdateWMS_CKPDSL(int uid,decimal sl,string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update wms_ckpd set [ckpd_by]={uid},[ckpd_time]='{DateTime.Now}',[ckpd_ykzsl]=[ckpd_ykzsl]+{sl}\r\nwhere [ckpd_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
    }
}
